package com.brother.service;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.brother.database.DataBase;
import com.brother.model.Customer;
import com.brother.model.LatLngPoint;
import com.brother.model.Tracker;
import com.brother.util.CommonProperties;
import com.brother.util.Distance;
import com.brother.util.ReportTableUtils;

public class TrackerService extends BaseService{
	private Logger log = Logger.getLogger(this.getClass());
public Tracker getTrackerByFieldName(String fieldName,String value){
	Tracker t = null;
	String sql = "select t.*, u.name as userName from tracker t "+
	" inner join user u on u.userid=t.userid where t."+fieldName+"='"+value+"'";
	
	try {
		ResultSet rs = DataBase.query(sql);	
		if(rs.next()){
			t = new Tracker();
			this.setModel(t,rs);
			t.setUserName(rs.getString("userName"));
		}
	} catch (SQLException e) {
		Logger.getLogger(this.getClass()).error(e);
	}finally{
		DataBase.closeConnection();
	}
	return t;
}

public void updateAllMileage(){
	List<Tracker> trackers = this.getAll();
	for(Tracker tker:trackers){
		if(tker.getMileUpdate()==null || (System.currentTimeMillis() - tker.getMileUpdate().getTime()) > 
					CommonProperties.getMileageUpdateTime()*1000){ //  超过里程更新时间
			this.updateMileage(tker.getTrackerid());
		}
	}
}

public void updateMileage(String trackerId){
	
	try {
		Tracker t = this.getTrackerByFieldName("trackerId", trackerId);
		String sql ="select SQL_BUFFER_RESULT signed_lat,signed_lng from r_"+t.getUserTrackerId()+"  " +
		" order by createTime asc";
		ResultSet rs = DataBase.query(sql);
		double mileage = 0;
		boolean first = true;
		LatLngPoint from = new LatLngPoint();
		LatLngPoint to = new LatLngPoint();
		while(rs.next()){
			float lat = rs.getFloat(1);
			float lng = rs.getFloat(2);
			to.setLat(lat);
			to.setLng(lng);
			if(!first){
				mileage = mileage + Distance.distance(from, to);
			}
			from.setLat(lat);
			from.setLng(lng);
			first = false;
		}
		int mile = (int)(mileage / 1000);
		this.setMileage(trackerId, mile);
	} catch (SQLException e) {
		log.error("caculating mileage error!",e);
	}
}

public void setMileage(String trackerId, float mileage){
	String sql ="update tracker set mileage=("+mileage
	+"-IFNULL(mileageDiff,0)),mileUpdate=now() where trackerId='"+trackerId+"'";
	DataBase.update(sql);
}



public void updateMileage_back(String trackerId){
	
	try {
		Tracker t = this.getTrackerByFieldName("trackerId", trackerId);
		String sql ="select SQL_BUFFER_RESULT signed_lat,signed_lng from r_"+t.getUserTrackerId()+"  " +
		" order by createTime asc";
		ResultSet rs = DataBase.query(sql);
		double mileage = 0;
		boolean first = true;
		LatLngPoint from = new LatLngPoint();
		LatLngPoint to = new LatLngPoint();
		while(rs.next()){
			float lat = rs.getFloat(1);
			float lng = rs.getFloat(2);
			to.setLat(lat);
			to.setLng(lng);
			if(!first){
				mileage = mileage + Distance.distance(from, to);
			}
			from.setLat(lat);
			from.setLng(lng);
			first = false;
		}
		int mile = (int)(mileage / 1000);
		this.setMileage(trackerId, mile);
	} catch (SQLException e) {
		log.error("caculating mileage error!",e);
	}
}

public void setMileage_back(String trackerId, float mileage){
	String sql ="update tracker set mileage="+mileage
	+",mileUpdate=now() where trackerId='"+trackerId+"'";
	DataBase.update(sql);
}
//mao
public void resetMileage(String trackerId, float mileage){
	String sql ="update tracker set mileage="+mileage+",mileageDiff=(IFNULL(mileage,0)-"+mileage
	+") where trackerId='"+trackerId+"'";
	DataBase.update(sql);
}

public int getReportInterval(String trackerId){
	String sql ="select reportInterval from tracker  where trackerid='"+trackerId+"'";
	
	try {
		ResultSet rs = DataBase.query(sql);
		return rs.getInt("reportInterval");
	} catch (SQLException e) {
		log.error(sql,e);
	}
	return 10;
}
	
private void setModel(Tracker t, ResultSet rs) throws SQLException {
	t.setCarmodelId(rs.getString("carmodelid"));
	t.setTrackerid(rs.getString("trackerid"));
	t.setUserid(rs.getInt("userid"));
	t.setCreateDate(rs.getDate("createdate"));
	t.setUpdateTime(rs.getDate("updatetime"));
	t.setModelName(rs.getString("modelName"));
	t.setUserTrackerId(rs.getString("userTrackerId"));
	t.setTrackerName(rs.getString("trackerName"));
	t.setReportInterval(rs.getInt("reportInterval"));
	t.setDriverID(rs.getString("driverID"));
	t.setDriverName(rs.getString("driverName"));
	t.setDriverMobile(rs.getString("driverMobile"));
	t.setRemark(rs.getString("remark"));
	t.setAccStat(rs.getInt("accStat"));
	t.setDoorStat(rs.getInt("doorStat"));
	t.setMailEnabled(rs.getBoolean("MailEnabled"));
	t.setMileage(rs.getFloat("mileage"));
	t.setMileUpdate(rs.getTimestamp("mileUpdate"));
	t.setBattery(rs.getString("battery"));
	t.setGsm(rs.getInt("gsm"));
}



public List<Tracker> getAll(){
	List<Tracker> result = new ArrayList();
	String sql = "select t.* from tracker t ";
	
	try {
		ResultSet rs = DataBase.query(sql);
		while(rs.next()){
			Tracker t = new Tracker();
			this.setModel(t,rs);
			result.add(t);
		}
	} catch (SQLException e) {
		Logger.getLogger(this.getClass()).error(e);
	}
	DataBase.closeConnection();
	return result;
}



public List<Tracker> findUserTracker(int userid){
	List<Tracker> result = new ArrayList();
	
	UserService userService = new UserService();
	Customer c = userService.GetUserByUserId(userid);
	if(c == null)
		return result;
	String sql = "select t.* from tracker t "+
	" where 1=1 ";
	//if(!c.isAdmin()){
		sql = sql + " and t.userid="+userid ;
		//+" or t.userid in (select userid from user where SUBSTRING_INDEX(parentid,',',-1)='"
	//	+userid+"')) ";
	//}
	

	
	if(log.isDebugEnabled()){
		log.debug(sql);
	}
	
	try {
		ResultSet rs = DataBase.query(sql);
		while(rs.next()){
			Tracker t = new Tracker();
			this.setModel(t, rs);
			result.add(t);
		}
	} catch (SQLException e) {
		Logger.getLogger(this.getClass()).error(e);
	}
	DataBase.closeConnection();
	return result;
}


public List<Tracker> findChildrenTracker(int userid, String userName, String trackerName, String userTrackerId){
	List<Tracker> result = new ArrayList();
	
	UserService userService = new UserService();
	Customer c = userService.GetUserByUserId(userid);
	if(c == null)
		return result;
	String sql = "select t.*, u.name as userName from tracker t "+
	" inner join user u on u.userid=t.userid where 1=1 ";
	//if(!c.isAdmin()){
		sql = sql + " and (t.userid="+userid 
		+" or t.userid in (select userid from user where  (parentid='"+userid
			+"' or parentid like '"+userid+",%' or parentid like '%,"
			+userid+"' or parentid like '%,"+userid+",%'))) ";
	//}
	
	if(userName != null && !userName.isEmpty()){
		sql = sql +" and u.name like '"+userName+"%' ";
	}
	
	if(trackerName != null && !trackerName.isEmpty()){
		try {
			trackerName = URLDecoder.decode(trackerName, "utf-8");
		} catch (UnsupportedEncodingException e) {
			log.error(e);
		}
		
		sql = sql +" and (t.trackername  like '"+trackerName+"%' or t.userTrackerid like '"+trackerName+"%')";
	}
	if(userTrackerId != null && !userTrackerId.isEmpty()){
		sql = sql +" and t.userTrackerId  like '"+userTrackerId+"%' ";
	}
	sql = sql + " order by t.trackername asc";
	
	if(log.isDebugEnabled()){
		log.debug(sql);
	}
	
	try {
		ResultSet rs = DataBase.query(sql);
		while(rs.next()){
			Tracker t = new Tracker();
			this.setModel(t, rs);
			t.setUserName(rs.getString("userName"));
			result.add(t);
		}
	} catch (SQLException e) {
		Logger.getLogger(this.getClass()).error(e);
	}
	DataBase.closeConnection();
	return result;
}

public boolean addTracker(String name, String carmodelId,String usertrackerId,String userid){
	String trackerId = UUID.randomUUID().toString();
	String sql = "insert into tracker(trackerid,userid,trackername,createdate,carmodelid,userTrackerId) "+
	   " values('"+trackerId+"','"+userid+"','"+StringUtils.trim(name)+"',now(),'"+carmodelId+"','"+usertrackerId+"')";
	if(Logger.getLogger(this.getClass()).isDebugEnabled())
		Logger.getLogger(this.getClass()).debug(sql);
	DataBase.update(sql);
	DataBase.closeConnection();
	//RealDataService rs = new RealDataService();
	//rs.insertSampleData(trackerId, userid);
	return true;
}

public boolean updateTracker(String name,String carmodelId,String usertrackerId,String driverId,
		String driverName,String driverMobile,String remark, String trackerId){
	String sql ="update tracker set trackername='"+StringUtils.trim(name)+"',carmodelId='"+carmodelId+"',usertrackerId='"+usertrackerId
	+"',driverId='"+driverId+"',driverName='"+driverName+"',driverMobile='"+driverMobile+"'," +
			"remark='"+ escapeDBField(remark)+"'  where trackerid='"+trackerId+"'";
	if(Logger.getLogger(this.getClass()).isDebugEnabled())
		Logger.getLogger(this.getClass()).debug(sql);
	DataBase.update(sql);
	DataBase.closeConnection();	
	return true;
}

public boolean updateTracker(Tracker tracker){
	escapeAllFields(tracker);
	String sql ="update tracker set trackername='"+StringUtils.trim(tracker.getTrackerName())
	+"',usertrackerId='"+tracker.getUserTrackerId()
	+"',driverId='"+tracker.getDriverID()+"',driverName='"+tracker.getDriverName()
	+"',driverMobile='"+tracker.getDriverMobile()+"'"
	+",carmodelId='"+tracker.getCarmodelId()+"'," +
			"remark='"+tracker.getRemark()+"',userid="+tracker.getUserid()
			+",mailEnabled="+(tracker.isMailEnabled() ? 1 : 0)+",modelName='"+tracker.getModelName()
			+"'"+"  where trackerid='"+tracker.getTrackerid()+"'";
	
	sql = sql.replaceAll("'null'", "''");
	if(Logger.getLogger(this.getClass()).isDebugEnabled())
		Logger.getLogger(this.getClass()).debug(sql);
	DataBase.update(sql);
	DataBase.closeConnection();	
	return true;
}


public boolean addTracker(Tracker tracker){
	this.escapeAllFields(tracker);
	String trackerId = UUID.randomUUID().toString();
	String sql = "insert into tracker(trackerid,userid,trackername,createdate,carmodelid," +
			"userTrackerId,driverId,driverName,driverMobile,remark, mailEnabled,modelName) "+
	   " values('"+trackerId+"','"+tracker.getUserid()+"','"+StringUtils.trim(tracker.getTrackerName())+"',now(),'"+tracker.getCarmodelId()+"','"
	   +tracker.getUserTrackerId()+"','"+tracker.getDriverID()+"','"+tracker.getDriverName()
	   +"','"+tracker.getDriverMobile()+"','"+tracker.getRemark()+"',"+(tracker.isMailEnabled() ? 1 : 0)+",'"+tracker.getModelName()+"')";
	
	sql = sql.replaceAll("'null'", "''");
	
	if(Logger.getLogger(this.getClass()).isDebugEnabled())
		Logger.getLogger(this.getClass()).debug(sql);
	DataBase.update(sql);
	DataBase.closeConnection();
	//RealDataService rs = new RealDataService();
	//rs.insertSampleData(trackerId, userid);
	return true;
}

public void updateTrackerReportInterval(String trackerId, String interval){
	String sql ="update tracker set reportInterval='"+interval +"',setSuccess=0  where trackerid='"+trackerId+"'";
	if(Logger.getLogger(this.getClass()).isDebugEnabled())
		Logger.getLogger(this.getClass()).debug(sql);
	DataBase.update(sql);
	DataBase.closeConnection();		
}

public void updateAccStatus(String trackerId, String status){
	String sql ="update tracker set accStat="+status +"  where trackerid='"+trackerId+"'";
	if(Logger.getLogger(this.getClass()).isDebugEnabled())
		Logger.getLogger(this.getClass()).debug(sql);
	DataBase.update(sql);
	DataBase.closeConnection();		
}

public void updateDoorStatus(String trackerId, String status){
	String sql ="update tracker set doorStat="+status +"  where trackerid='"+trackerId+"'";
	if(Logger.getLogger(this.getClass()).isDebugEnabled())
		Logger.getLogger(this.getClass()).debug(sql);
	DataBase.update(sql);
	DataBase.closeConnection();		
}

public void deleteTracker(String trackerid){
	Tracker t = this.getTrackerByFieldName("trackerid", trackerid);
	String sql = "delete from tracker where trackerid='"+trackerid+"'";
	log.debug(sql);
	DataBase.update(sql);
	sql = "delete from realtimedata where trackerid='"+trackerid+"'";
	log.debug(sql);
	DataBase.update(sql);
	
	sql = "drop table r_"+t.getUserTrackerId(); // 删除数据表格
	if(log.isDebugEnabled()){
		log.debug(sql);
	}
	DataBase.update(sql);
	ReportTableUtils.getInstance().setImei(t.getUserTrackerId(), false);
	DataBase.closeConnection();
}

/**Set it to be 1 if the user action that set the report interval successfully.
 * @param i
 */
public void updateIntervalSetStatus(int i,String trackerId) {
	String sql ="update tracker set setSuccess="+i+"  where trackerid='"+trackerId+"'";
	if(log.isDebugEnabled())
		log.debug(sql);
	DataBase.update(sql);
	DataBase.closeConnection();	
	
}

public int getIntervalSetStatus(String trackerId){
	String sql ="select setSuccess from tracker where trackerid='"+trackerId+"'";
	if(log.isDebugEnabled())
		log.debug(sql);
	
	int value = 0;
	try {
		ResultSet rs = DataBase.query(sql);
		if(rs.next()){
			value = rs.getInt("setSuccess");
		}
	} catch (SQLException e) {
		log.error(e);
	}
	DataBase.closeConnection();
	
	return value;
}


}
